﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class Products : System.Web.UI.Page
{
    SqlConnection con;
    SqlDataAdapter da;
    DataSet ds;
    DataTable dt;
    DataView dv;
    SqlCommand cmd;
    SqlCommandBuilder cb;
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            if (!this.IsPostBack)
            {
                Session["searchProducts"] = false;
                try
                {
                    loadData();
                    ViewState["gv"] = gvProducts.DataSource;
                    ViewState["SortDirection"] = "DESC";
                }
                catch (System.Exception ex)
                {
                    Response.Write("<script>alert('Load DB failed')</script>");
                }
            }

            gvProducts.DataSource = ViewState["gv"];
            gvProducts.DataBind();

            if ((bool)Session["searchProducts"])
            {
                btnDelete.Visible = true;
                btnSave.Visible = true;
                btnNew.Visible = true;
                btnAdd.Visible = false;
            }
            else
            {
                btnDelete.Visible = false;
                btnSave.Visible = false;
                btnNew.Visible = true;
                btnAdd.Visible = true;
            }
            btnSave.Attributes.Add("onclick", "if(!confirm('Are you sure?')) return false;");
            btnDelete.Attributes.Add("onclick", "if(!confirm('Are you sure?')) return false;");
        }
        catch (System.Exception ex)
        {
            Response.Write("<script>alert('Some thing wrong!')</script>");
        }
       
    }
    protected void loadData()
    {
        try
        {
            con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            con.Open();
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "Production.getProducts";
            da = new SqlDataAdapter(cmd);
            cb = new SqlCommandBuilder(da);
            ds = new DataSet();
            da.FillSchema(ds, SchemaType.Mapped);
            da.Fill(ds, "Products");
            dt = ds.Tables["Products"];
            dv = new DataView(dt);
            gvProducts.DataSource = dt;
            gvProducts.AutoGenerateSelectButton = true;
            gvProducts.AllowPaging = true;
            gvProducts.PageSize = 15;
            gvProducts.DataBind();
            ViewState["gv"] = gvProducts.DataSource;
        }
        catch (System.Exception ex)
        {
            Response.Write("<script>alert('Load DB failed')</script>");
            Response.Redirect("404.aspx");
        }
        finally
        {
            con.Close();
        }
    }


    protected void clear()
    {
        txtProductID.Text = null;
        txtProductName.Text = null;
        ddlCategoryName.SelectedValue = null;
        ddlSupplierName.SelectedValue = null;
        txtPrice.Text = null;
        txtSearch.Text = null;
        txtSearch.Enabled = true;
        ddlSearchBy.Enabled = true;
        chkDiscontinued.Checked = false;
        chkSearchDiscontinued.Visible = false;
        btnDelete.Visible = false;
        btnSave.Visible = false;
        btnNew.Visible = true;
        btnAdd.Visible = true;
        loadData();
    }
    protected void addProduct()
    {
        try
        {
            con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            con.Open();
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "Production.insertProducts";

            SqlParameter param = new SqlParameter("@productname", SqlDbType.NVarChar);
            param.Size = 40;
            param.Value = txtProductName.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@supplierid", SqlDbType.Int);
            param.Value = ddlSupplierName.SelectedValue;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@categoryid", SqlDbType.Int);
            param.Value = ddlCategoryName.SelectedValue;
            cmd.Parameters.Add(param);


            param = new SqlParameter("@unitprice", SqlDbType.Money);
            param.Value = txtPrice.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@discontinued", SqlDbType.Bit);
            param.Value = chkDiscontinued.Checked;
            cmd.Parameters.Add(param);
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            Response.Write("<script>alert('" + "Add failed" + "')</script>");
        }
        finally
        {
            con.Close();
            loadData();
            clear();
        }
    }

    protected void uppdateProdct()
    {
        try
        {
            con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            con.Open();
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "Production.updateProducts";

            SqlParameter param = new SqlParameter("@productid", SqlDbType.Int);
            param.Value = int.Parse(txtProductID.Text);
            cmd.Parameters.Add(param);

            param = new SqlParameter("@productname", SqlDbType.NVarChar);
            param.Size = 40;
            param.Value = txtProductName.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@supplierid", SqlDbType.Int);
            param.Value = ddlSupplierName.SelectedValue;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@categoryid", SqlDbType.Int);
            param.Value = ddlCategoryName.SelectedValue;
            cmd.Parameters.Add(param);


            param = new SqlParameter("@unitprice", SqlDbType.Money);
            param.Value = txtPrice.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@discontinued", SqlDbType.Bit);
            param.Value = chkDiscontinued.Checked;
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
           
        }
        catch (System.Exception ex)
        {
            Response.Write("<script>alert('" + "Update failed" + "')</script>");
        }
        finally
        {
            con.Close();
            loadData();
            clear();
        }
    }
    protected void deleteProduct()
    {
        try
        {
            con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            con.Open();
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "Production.delProducts";
            SqlParameter param = new SqlParameter("@productid", SqlDbType.Int);
            param.Value = int.Parse(txtProductID.Text);
            cmd.Parameters.Add(param);
            cmd.ExecuteNonQuery();
            loadData();
        }
        catch (System.Exception ex)
        {
            Response.Write("<script>alert('" + "Delete failed" + "')</script>");
        }
        finally
        {
            con.Close();
            clear();
        }

    }
    protected void searchBy()
    {
        try
        {
            loadData();
            ViewState["isSearch"] = true;
            #region searchmethod
            if (ddlSearchBy.Text.Equals("ID"))
            {
                try
                {
                    dv = new DataView(dt);
                    dv.RowFilter = string.Format("[Product ID] = {0}", int.Parse(txtSearch.Text.Trim()));
                    gvProducts.DataSource = dv;
                    gvProducts.DataBind();
                }
                catch (System.Exception ex)
                {
                    gvProducts.DataSource = null;
                    gvProducts.DataBind();
                }

            }
            if (ddlSearchBy.Text.Equals("Product Name"))
            {
                dv = new DataView(dt);
                dv.RowFilter = string.Format("[Product Name] LIKE '%{0}%'", txtSearch.Text.Trim());
                gvProducts.DataSource = dv;
                gvProducts.DataBind();
            }
            if (ddlSearchBy.Text.Equals("Category Name"))
            {
                dv = new DataView(dt);
                dv.RowFilter = string.Format("[Category Name] LIKE '%{0}%'", txtSearch.Text.Trim());
                gvProducts.DataSource = dv;
                gvProducts.DataBind();
            }
            if (ddlSearchBy.Text.Equals("Supplier Name"))
            {
                dv = new DataView(dt);
                dv.RowFilter = string.Format("[Suplier Name] LIKE '%{0}%'", txtSearch.Text.Trim());
                gvProducts.DataSource = dv;
                gvProducts.DataBind();
            }
            if (ddlSearchBy.Text.Equals("Price"))
            {
                try
                {
                    dv = new DataView(dt);
                    dv.RowFilter = string.Format("[Unit Price] = {0}", Double.Parse(txtSearch.Text));
                    gvProducts.DataSource = dv;
                    gvProducts.DataBind();
                }
                catch (System.Exception ex)
                {
                    gvProducts.DataSource = null;
                    gvProducts.DataBind();
                }

            }
            if (ddlSearchBy.Text.Equals("Discontinued"))
            {
                dv = new DataView(dt);
                dv.RowFilter = string.Format("[Discontinued] = {0}", chkSearchDiscontinued.Checked);
                gvProducts.DataSource = dv;
                gvProducts.DataBind();
            }
            if (ddlSearchBy.Text.Equals("Supplier ID"))
            {
                try
                {
                    dv = new DataView(dt);
                    dv.RowFilter = string.Format("[Supplier ID] = {0}", int.Parse(txtSearch.Text));
                    gvProducts.DataSource = dv;
                    gvProducts.DataBind();
                }
                catch (System.Exception ex)
                {
                    gvProducts.DataSource = null;
                    gvProducts.DataBind();
                }

            }
            if (ddlSearchBy.Text.Equals("Category ID"))
            {
                try
                {
                    dv = new DataView(dt);
                    dv.RowFilter = string.Format("[Category ID] = {0}", int.Parse(txtSearch.Text));
                    gvProducts.DataSource = dv;
                    gvProducts.DataBind();
                }
                catch (System.Exception ex)
                {
                    gvProducts.DataSource = null;
                    gvProducts.DataBind();
                }

            }
            ViewState["gv"] = ((DataView)gvProducts.DataSource).ToTable();
#endregion
        }
        catch (System.Exception ex)
        {
            Response.Write("<script>alert('" + "Search failed" + "')</script>");
        }
        finally
        {
            Session["searchProducts"] = true;
            
        }
    }

    protected void gvProducts_SelectedIndexChanged(object sender, EventArgs e)
    {
        try
        {
            GridViewRow r = gvProducts.SelectedRow;
            txtProductID.Text = Server.HtmlDecode(r.Cells[1].Text);
            txtProductName.Text = Server.HtmlDecode(r.Cells[2].Text);
            ddlCategoryName.SelectedValue = Server.HtmlDecode(r.Cells[4].Text);
            ddlSupplierName.SelectedValue = Server.HtmlDecode(r.Cells[3].Text);
            CheckBox discontinued = (CheckBox)r.Cells[6].Controls[0];
            chkDiscontinued.Checked = discontinued.Checked;
            txtPrice.Text = Server.HtmlDecode(r.Cells[5].Text);
            btnDelete.Visible = true;
            btnSave.Visible = true;
            btnNew.Visible = true;
            btnAdd.Visible = false;
        }
        catch (System.Exception ex)
        {
            Response.Write("<script>alert('" + "Selected index change failed" + "')</script>");
        }
        

    }
    protected void gvProducts_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        try
        {
            gvProducts.PageIndex = e.NewPageIndex;
            gvProducts.DataSource = ViewState["gv"];
            gvProducts.DataBind();

            txtProductID.Text = null;
            txtProductName.Text = null;
            ddlCategoryName.SelectedValue = null;
            ddlSupplierName.SelectedValue = null;
            txtPrice.Text = null;
            txtSearch.Text = null;
            txtSearch.Enabled = true;
            ddlSearchBy.Enabled = true;
            chkDiscontinued.Checked = false;
            btnDelete.Visible = false;
            btnSave.Visible = false;
            btnNew.Visible = true;
            btnAdd.Visible = true;
        }
        catch (System.Exception ex)
        {
            Response.Write("<script>alert('" + "Page index changing failed" + "')</script>");
        }
       
    }
    protected void btnNew_Click(object sender, EventArgs e)
    {
        clear();
        loadData();
    }
    protected void btnAdd_Click(object sender, EventArgs e)
    {
        addProduct();
    }
   
    
    protected void btnSearch_Click(object sender, EventArgs e)
    {
        searchBy();
    }

    protected void ddlSearchBy_TextChanged(object sender, EventArgs e)
    {
        try
        {
            if (ddlSearchBy.Text.Equals("Discontinued"))
            {
                txtSearch.Visible = false;
                chkSearchDiscontinued.Visible = true;
            }
            else
            {
                txtSearch.Visible = true;
                chkSearchDiscontinued.Visible = false;
            }
        }
        catch (System.Exception ex)
        {
            Response.Write("<script>alert('" + "SearchBy textchanged failed" + "')</script>");
        }
        
    }

    protected void btnDelete_Click(object sender, EventArgs e)
    {
        deleteProduct();
    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        uppdateProdct();
    }
    protected void btnRefresh_Click(object sender, EventArgs e)
    {
        clear();
        loadData();
    }
    protected void gvProducts_Sorting(object sender, GridViewSortEventArgs e)
    {
        try
        {
            DataTable dataTable = ViewState["gv"] as DataTable;

            if (dataTable != null)
            {
                DataView dataView = new DataView(dataTable);
                if (ViewState["SortDirection"].ToString().Trim().Equals("ASC"))
                {
                    dataView.Sort = e.SortExpression + " " + "DESC";
                    ViewState["SortDirection"] = "DESC";
                }
                else
                {
                    dataView.Sort = e.SortExpression + " " + "ASC";
                    ViewState["SortDirection"] = "ASC";
                }
                gvProducts.DataSource = dataView;
                gvProducts.DataBind();
                ViewState["gv"] = dataView.ToTable();
            }        
        }
        catch (System.Exception ex)
        {
            Response.Write("<script>alert('" + "Product sorting failed" + "')</script>");
        }
        
    }
}
